Skip to main content

Writing to the Database

After setting up Node-Red and the Common Configurator, you can now start to create the Write-Block.

Everything needed to write to the Database​

To write to the Database from the Flow Creator, you need the following:

  • the correct Database configuration to write to the Database
  • the correct publishTopic, also called pubTopic
  • the correct Datapoint ID from the Metadata Topic, also called dataTopic

You can find which topics are correct for your database configuration in our documentation under Topics or under each DB's "Connector Topics".
In this case it is MSSQL. The most important part when sending a message payload is that it has to have the correct MQTT Broker Data topic "pubTopic". In this example, the pubTopic for the MSSQL Connector is "ie/d/j/simatic/v1/sqlc1/dp/w/dev server/ALL_DATATYPES".
The pubTopic for your Database Connector can be found below the Metadata Topic in the Documentation. In this case it is the "sqlc1" MSSQL Connector, the "dp" Datapoint, the "w" for write. Just using these and adding a "#" to it would write to all write-datatopics.
To compare the metadata topic from the database configurator to the one used, use a "mqtt in" block and a Debug block. The "mqtt in" block needs the correct databus-server and the action "Subscribe to a single topic", the topic below is just "ie/m/j/simatic/v1/sqlc1/dp".
Deploying this and opening a message with the node:metadata should look like this:

metadata

Here you can see the used topic, pubTopic and statusTopic.
If you open the dataPointDefinitions-array, you get the needed id to write to each Datapoint configured in the Common Configurator.

DP id

Creating a Write-Block​

After finding the correct topics and ids, you can create an actual Write-block.
To add a new Output, take an "Inject" box and drag it to the corresponding Connector. Then also drag an "mqtt out", a "debug" and a "function" to the field and connect them with the timestamp.

Flow Creator New Block

To edit the "mqtt out", double-click it and write in the correct Databus-Server, Topic and Name for the Output. The Topic is the same as used above, just switch out the "ALL_DATATYPES" with the table you want to write to.
The function can also be edited with a double-click to customize the message. The message-payload needs to have the correct structure, as seen in Payload Structure Definition. For the Datapoint-ids you need the ids from Everything needed to write to the DB, for example "DP1" and "DP3".

After any change in the Flow Creator, the "Deploy" button on the top right will turn red. After re-deploying it, click on the Button next to the "Inject" block. A new message should show up in the Debug Output with the two DPs from the function payload:

write-to-DB

If this doesn't happen, check that the button to the right of the debug-output is saturated green and active. Only then the debug-log will actually be output.

Status of the Databus Connection​

To check the status of your Connection with the Databus, you can also drag a "mqtt-in" and a "Debug" block into the Flow. The "mqtt-in" is set to your Databus-Server and as an Action "Subscribe to single topic". The topic below is "ie/s/j/simatic/v1/sqlc1/status", the statustopic of the MSSQL Connector. the "Debug" block is set to "msg. payload" again.
After deploying, you might get a debug message with a status payload similar to this:

Metadata Output

So if you are not sure if the databus is working and online, the status object is the first thing to look at.

Payload Structure Definition​

The sent payload is an Object with an Array of id-value pairs and has to have this structure:

payload: {
    "seq": 0,
    "vals": [
        {
            "id": "DP1",
            "val": "1"
        },
        {
            "id": "DP3",
            "val": "7"
        }
    ]
}

If the id is set to auto-increment in the Database, then the id field can be left blank. Otherwise, the Datapoint id has to be higher than the ids of the datapoints already in the Database.

Further Information​

To not make this documentation too long, only the most important points have been explained. For a full Configuration of the Database in the Common Configurator please refer to the Setup-Video in the Welcome Section. Use your own Database data and mqtt user with the correct permission. The rest of the documentation in more detail can be found under each DB kind of the SQL Connector.